#!/bin/bash

#定义变量：数据同步日期，标识同步哪一天日志数据
#第1，执行shell脚本时，传递参数
#第2，如果没有传递参数，同步前一天数据
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

ADS_TRAFFIC_VIEW_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_app_ads.ads_traffic_page_view_report
SELECT
      dt
     ,recent_days
     ,province
     ,city
     ,page_view_count
     ,user_count
     ,session_count
     ,avg_user_session_count
     ,session_during_time
     ,avg_session_during_time
     ,bounce_count
     ,bounce_count_rate
FROM jtp_app_ads.ads_traffic_page_view_report
UNION
SELECT
    '${data_date}' AS dt
     ,1 AS recent_days
     ,province
     ,city
     ,sum(session_pv) AS page_view_count
     ,count(DISTINCT device_id) AS user_count
     ,count(session_id) AS session_count
     ,round(count(session_id)/count(DISTINCT device_id),2) AS avg_user_session_count
     ,round(sum(session_during_time)/1000/60,2) AS session_during_time
     ,round(avg(session_during_time)/1000/60,2) AS avg_session_during_time
     ,sum(if(session_pv=1,1,0)) AS bounce_count
     ,round(sum(if(session_pv=1,1,0))/count(session_pv),4) AS bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt='${data_date}' AND province IS NOT NULL AND city IS NOT NULL
GROUP BY province,city;
"

#执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_TRAFFIC_VIEW_REPORT_SQL}"
